<?php

/**
 * The Home Page
 *
 * @since ADD ADOdb Admin 0.0
 */
CLASS ctrl_page_index EXTENDS ctrl_tpl_adodb_admin {

   public $common_gpc = array(
         '_REQUEST' => array('mode','db','table','page','ajax')
      );

   public $mode_gpc_query = array(
         '_REQUEST' => array('sql'),
         '_SESSION' => array('paginated_sql')
      );

   public $mode_gpc_export_csv = array(
         '_REQUEST' => array('sql'),
         '_SESSION' => array('paginated_sql')
      );

   public $mode_gpc_show_tables = array(
         '_REQUEST' => array('db')
      );

   public $mode_gpc_show_databases = array();

   public $mode_gpc_select_database = array(
         '_REQUEST' => array('database')
      );

   public $data = array(
         'row_actions' => array()
      );

   protected $adodb;

   /**
    * Set the adodb property
    *
    * @since ADD ADOdb Admin 0.0
    */
   public function pre_mode_process($gpc) {
      parent::pre_mode_process($gpc);
      $default_mode = 'show_databases';
      if (empty(adodb_user::current_logged_in()->database) && !in_array($gpc['mode'],array($default_mode,'select_database'))) {
         add::redirect("?mode=$default_mode");
      }
      $this->adodb = adodb_user::current_logged_in()->adodb();
   }


   /**
    * Query mode
    *
    * @since ADD ADOdb Admin 0.0
    */
   public function process_mode_query($gpc) {
      extract($gpc);

      $page = $page > 0 ? (int) $page : 1;

      $per_page = isset(add::config()->rows_per_page) ? add::config()->rows_per_page : 100;

      if ($sub_mode == 'paginated_query' && empty($sql)) {
         $sql = $paginated_sql;
      }

      try {
         $result = $this->adodb->Execute($sql);
      }
      catch (e_database $e) {
         throw new e_user_input($e->getMessage());
      }

      if (!$result && $this->adodb->ErrorMsg()) {
         throw new e_user($this->adodb->ErrorMsg());
      }

      if ( $result instanceof ADORecordSet_empty ) {

         $this->assign(
               'json_data',
               array(
                     'type' => 'message',
                     'message' => 'Query Successfully executed. rows affected: '.$this->adodb->Affected_Rows()
                  )
            );
      }
      else if ($result) {

         if ($page > 1) {
            $result->Move( $per_page * ($page -1) );
         }

         $this->assign("rows",$result->getArray($per_page));

         if ($this->data['rows']) {

            $this->assign("fields",array_keys((array)$this->data['rows'][0]));
            $pagination_get = $gpc;
            unset($pagination_get['ajax']);

            if ($_POST) {
               $pagination_get['sub_mode'] = 'paginated_query';
               $_SESSION['paginated_sql'] = $sql;
               unset($pagination_get['sql']);
            }

            unset($pagination_get['paginated_sql']);

            $pagination_base = http_build_query($pagination_get);
            $this->assign("pagination", new pagination($result->RecordCount(),$page,$per_page, "?".$pagination_base ,'page',false));

            if (!empty($_REQUEST['ajax'])) {
               $this->table_ajax();
            }

         }
         else {

            $this->assign(
                  'json_data',
                  array(
                        'type' => 'message',
                        'message' => 'Query returned a blank result set'
                     )
               );

         }
      }
      else {
         throw new e_user_input("Failed to execute query");
      }
   }

   /**
    * Query mode
    *
    * @since ADD ADOdb Admin 0.0
    */
   public function process_mode_export_csv($gpc) {
      extract($gpc);

      $page = $page > 0 ? (int) $page : 1;
      $per_page = 10;

      if ($sub_mode == 'continuous_export' && empty($sql)) {
         $sql = $paginated_sql;
      }

      $tmp_file_path =  sys_get_temp_dir(). "/add_adodb_admin_".sha1($sql).".csv.tmp";

      if ($sub_mode == 'download') {
         header('Content-disposition: attachment; filename=sql_'.date('Ymdhi').'.csv');
         header('Content-type: application/csv');
         readfile($tmp_file_path);
         unlink($tmp_file_path);
         add::shutdown(false);
      }

      try {
         $result = $this->adodb->Execute($sql);
      }
      catch (e_database $e) {
         throw new e_user_input($e->getMessage());
      }

      if (!$result && $this->adodb->ErrorMsg()) {
         throw new e_user($this->adodb->ErrorMsg());
      }

      if ( $result instanceof ADORecordSet_empty ) {
         $this->assign(
               'json_data',
               array(
                     'type' => 'message',
                     'message' => 'Query Successfully executed. rows affected: '.$this->adodb->Affected_Rows()
                  )
            );
      }
      else if ($result) {

         $this->assign('total_count',$result->RecordCount());

         set_time_limit(120);
         $max_execution_time = min(ini_get('max_execution_time')/2,10);
         $start_time         = microtime(true);;

         if ($this->data['total_count']) {
            do {
               if ($page > 1) {
                  $result->Move( $per_page * ($page - 1) );
               }
               $this->assign("rows",$result->getArray($per_page));

               $this->assign("fields",array_keys((array)$this->data['rows'][0]));
               $pagination_get = $gpc;
               #unset($pagination_get['ajax']);

               if ($_POST) {
                  $pagination_get['sub_mode'] = 'continuous_export';
                  $_SESSION['paginated_sql'] = $sql;
                  unset($pagination_get['sql']);
               }

               unset($pagination_get['paginated_sql']);

               if ($page == 1 || !file_exists($tmp_file_path)) {
                  $is_first_page = true;
               }
               else {
                  $is_first_page = false;
               }

               if ($is_first_page) {
                  $tmp_file = fopen($tmp_file_path, 'w+');
                  fputcsv($tmp_file, array_keys($this->data['rows'][0]));
               }
               else {
                  $tmp_file = fopen($tmp_file_path, 'a+');
               }

               $proccessed_count       = count($this->data['rows'])+($per_page*($page-1));
               $pagination_get['page'] = ++$page;
               $is_last_page = ($proccessed_count >= $this->data['total_count']);

               $next_page_query = http_build_query($pagination_get);
               $this->assign( "next_page", "?".$next_page_query );

               foreach ($this->data['rows'] as $row) {
                  if (!fputcsv($tmp_file, $row)) {
                     throw new e_system("Failed to add row to csv ".json_encode($row));
                  }
               }

               if ($ajax) {

                  add::$handle_shutdown = false;

                  $this->assign(
                        'json_data',
                        array(
                              'type'      => 'eval',
                              'message'   => "Exporting Rows ".$proccessed_count."/".$this->data['total_count'],
                              'next_page' => $this->data['next_page'],
                              'script'    => '$.get(json.next_page,{ ajax:1 },function(data) { process_json(data,$target); });'
                           )
                     );
               }
               else {
                  add::redirect($this->data['next_page']);
               }

               if ($is_last_page) {

                  $pagination_get['sub_mode'] = 'download';

                  $download_page_query = http_build_query($pagination_get);
                  $this->assign( "download_page", "?".$download_page_query);

                  if ($ajax) {
                     add::$handle_shutdown = false;
                     #debug::var_dump($proccessed_count,$page, $this->data);
                     $this->assign(
                           'json_data',
                           array(
                                 'type'      => 'eval',
                                 'message'   => "Exporting Rows ".$proccessed_count."/".$this->data['total_count'],
                                 'next_page' => $this->data['download_page'],
                                 'script'    => 'location.href = json.next_page;'
                              )
                        );
                  }
                  else {
                     add::redirect($this->data['download_page']);
                  }
               }

            }
            while ( ($max_execution_time > abs(microtime(true)-$start_time))  && !$is_last_page);
            fclose($tmp_file);
         }
         else {

            $this->assign(
                  'json_data',
                  array(
                        'type' => 'message',
                        'message' => 'Query returned a blank result set'
                     )
               );

         }
      }
      else {
         throw new e_user_input("Failed to execute query");
      }
   }


   /**
    * Tables
    *
    * @since ADD ADOdb Admin 0.0
    */
   public function process_mode_show_tables($gpc) {
      extract($gpc);
      $tables = array();
      foreach ($this->adodb->MetaTables() as $table) {
         $tables[] = array('meta_table' => $table);
      }
      if (!$tables && $this->adodb->ErrorMsg())
         throw new e_user($this->adodb->ErrorMsg());


      $this->assign('rows',$tables);
      $this->assign('fields',array('meta_table'));

      if (!empty($_REQUEST['ajax'])) {
         $this->table_ajax();
      }
   }


   /**
    * Tables
    *
    * @since ADD ADOdb Admin 0.0
    */
   public function process_mode_show_databases($gpc) {
      extract($gpc);
      $databases = array();

      foreach ($this->adodb->MetaDatabases() as $database) {
         $databases[] = array('meta_database' => $database);
      }

      if (!$databases && $this->adodb->ErrorMsg())
         throw new e_user($this->adodb->ErrorMsg());


      $this->assign(
            'row_actions',
            array(
               new row_action(
                     array(
                           'inner_html' => 'Select Database',
                           'format'     => '?mode=select_database&database=%s',
                           'fields'     => array('meta_database')
                        )
                  )
               )
         );

      $this->assign('rows',$databases);

      $this->assign('fields',array('meta_database'));
      if (!empty($_REQUEST['ajax'])) {
         $this->table_ajax();
      }
   }


   /**
    * Select Database
    *
    * @since ADD ADOdb Admin 0.0
    */
   public function process_mode_select_database($gpc) {
      extract($gpc);
      adodb_user::current_logged_in()->database = $database;
      add::redirect('?mode=show_tables');
   }

   /**
    * Response when ajax
    *
    * @since ADD ADOdb Admin 0.0
    */
   public function table_ajax() {
      $json_data = array(
            'type'=>'eval',
            'rows' => &$this->data['rows'],
            'fields' => &$this->data['fields'],
            'row_actions' => &$this->data['row_actions'],
            'pagination' => (string) $this->data['pagination']
         );

         $json_data['script'] = <<<'EOT'
$table = $('<table><caption>SQL Results</caption><thead></thead><tbody></tbody><tfoot><tr><td colspan=999>'+json.pagination+'</td></tr></tfoot></table>');
$target.html($table);
$table.find('thead').html('<tr><th>'+json.fields.join('</th><th>')+'</th></tr>');
if (json.row_actions.length) {
   $table.find('thead tr').prepend('<th colspan="'+json.row_actions.length+'"></th>');
}
$tbody = $table.find('tbody');
for (var x in json.rows) {
   var row = json.rows[x];
   var tr = '<tr>';
   for (var action_name in json.row_actions) {
      var action = json.row_actions[action_name];
      var args = [];

      for ( var a = 1 ; action.format[a]; a++ ) {
         args[args.length] = row[action.format[a]];
      }

      tr += '<td><a href="'+vsprintf(action.format[0],args)+'">'+action.inner_html+'</a></td>';
   }
   for (var y in json.fields) {
      var field = json.fields[y];
      tr += '<td>'+row[field]+'</td>';
   }
   tr += '</tr>';
   $tbody.append(tr);
}
EOT;

      $this->assign('json_data',$json_data);
      add::$handle_shutdown = false;
   }
}